require("lsqlite3")

local db = sqlite3.open("items.db")

local REQ = [[
SELECT i.id, i.conjured, bt.name
FROM items i
JOIN itemSpells s ON s.item_id = i.id
JOIN bonusInstances bi ON s.bonus_id = bi.id
JOIN bonusTypes bt ON bi.bonus_id = bt.id
WHERE i.class_id = 0 and bt.name LIKE "%Restores % mana over % sec.%drinking%"
]]

local result = {
	conjured = {},
	spirit = {},
	basic = {},
}

local function write_result(name, data)
	local temp = {}
	local amounts = {}
	for amount, items in pairs(data) do
		table.sort(items)
		amounts[#amounts + 1] = amount
	end
	table.sort(amounts)
	for _, amount in ipairs(amounts) do
		for _, item in ipairs(data[amount]) do
			temp[#temp + 1] = item..":"..amount
		end
	end
	print(("\t[%q] = %q,"):format(name, table.concat(temp, ",")))
end

for id, conjured, bonus in db:urows(REQ) do
	local amount = tonumber(bonus:match("[Rr]estores (%d+) mana over %d+ sec%."))
	local has_spirit = bonus:match("Spirit")
	local tbl
	if has_spirit then
		tbl = result.spirit
	elseif tonumber(conjured) == 1 then
		tbl = result.conjured
	else
		tbl = result.basic
	end
	local t = tbl[amount]
	if not t then
		t = {}
		tbl[amount] = t
	end
	t[#t + 1] = id
end

write_result("Consumable.Water.Basic", result.basic)
write_result("Consumable.Water.Conjured", result.conjured)
write_result("Consumable.Water.Buff.Spirit", result.spirit)
